CREATE TABLE People4 (
  id int(11) unsigned NOT NULL auto_increment,
  fname varchar(255) default NULL,
  lname varchar(255) default NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE Shoe (
  id int(11) unsigned NOT NULL auto_increment,
  size decimal (3,1)default NULL,
  peopleID int(11) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY peopleId (peopleId),
  CONSTRAINT Shoe_ibfk_1
    FOREIGN KEY (peopleId) REFERENCES People4 (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE FavColor (
  id int(11) unsigned NOT NULL auto_increment,
  color varchar (255)default NULL,
  peopleID int(11) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY peopleId (peopleId),
  CONSTRAINT FavColor_ibfk_1
    FOREIGN KEY (peopleId) REFERENCES People4 (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE CoinFlip (
  id int(11) unsigned NOT NULL auto_increment,
  results varchar (255)default NULL,
  position varchar (255)default NULL,
  peopleID int(11) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY peopleId (peopleId),
  CONSTRAINT CoinFlip_ibfk_1
    FOREIGN KEY (peopleId) REFERENCES People4 (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE DiceRoll (
  id int(11) unsigned NOT NULL auto_increment,
  results int(11) unsigned NULL,
  position int (11)default NULL,
  peopleID int(11) unsigned NOT NULL,
  PRIMARY KEY  (id),
  KEY peopleId (peopleId),
  CONSTRAINT DiceRoll_ibfk_1
    FOREIGN KEY (peopleId) REFERENCES People4 (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO People4 (fname, lname)
VALUES
  ('Chris', 'P'),
  ('Mihir', 'K'),
  ('Michael', 'D'),
  ('Ritvik', 'B');


INSERT INTO Shoe (size, peopleID)
VALUES
  (10, 1),
  (10.5, 2),
  (12, 3),
  (9, 4);


INSERT INTO FavColor (color, peopleID)
VALUES
  ('red', 1),
  ('red', 2),
  ('blue', 3),
  ('green', 4);


INSERT INTO CoinFlip (results, position, peopleID)
VALUES
  ('heads', 1, 1),
  ('tails', 2, 1),
  ('tails', 3, 1),
  ('tails', 1, 2),
  ('heads', 2, 2),
  ('heads', 1, 3),
  ('tails', 2, 3),
  ('heads', 3, 3),
  ('heads', 1, 4),
  ('heads', 2, 4),
  ('tails', 3, 4),
  ('heads', 4, 4),
  ('heads', 5, 4);


Select People4.fname, Shoe.size, FavColor.color
FROM CoinFlip
INNER JOIN People4 ON CoinFlip.peopleID = People4.id
INNER JOIN Shoe ON Shoe.peopleID = People4.id
INNER JOIN FavColor ON FavColor.peopleID = People4.id


Select People.fname, Shoe.size, FavColor.color
FROM DiceRoll
INNER JOIN People ON DiceRoll.peopleID = People.id
INNER JOIN Shoe ON Shoe.peopleID = People.id
INNER JOIN FavColor ON FavColor.peopleID = People.id


Select DiceRoll.peopleID, People.fname, People.lname, Shoe.size, FavColor.color, DiceRoll.results
FROM DiceRoll
INNER JOIN People ON DiceRoll.peopleID = People.id
INNER JOIN Shoe ON Shoe.peopleID = People.id
INNER JOIN FavColor ON FavColor.peopleID = People.id